﻿using System;
using System.Collections.Generic;
using System.Text;
using Stone.Data.Providers;
using System.Data;
using Dare.Utilities.Data;
using System.Data.Common;
using MySql.Data.MySqlClient;

namespace Stone.Data.MySql.Providers
{
    public class MySqlDatabaseDataProvider : DatabaseDataProvider
    {
        public override DataTable SelectAsTable(TransactionManager manager, string sql)
        {
            DataTable dt = null;
            Execute(manager, delegate(TransactionManager mgr, DbCommand cmd)
            {
                cmd.CommandText = sql;

                using (IDataReader reader = GenericDbHelper.ExecuteReader(mgr, cmd))
                {
                    dt = new DataTable();
                    dt.Load(reader);
                }
            });

            return dt;
        }

        public override int ExecuteNonQuery(TransactionManager manager, string sql)
        {
            int nRet = 0;
            Execute(manager, delegate(TransactionManager mgr, DbCommand cmd)
            {
                cmd.CommandText = sql;
                nRet = GenericDbHelper.ExecuteNonQuery(mgr, cmd);
            });

            return nRet;
        }

        public override T ExecuteScalar<T>(TransactionManager manager, string sql, T t)
        {
            T obj = default(T);
            Execute(manager, delegate(TransactionManager mgr, DbCommand cmd)
            {
                cmd.CommandText = sql;
                obj = GenericDbHelper.ExecuteScalar<T>(mgr, cmd, t);
            });

            return obj;
        }

        public override DateTime GetDateTime(TransactionManager manager)
        {
            DateTime dt = DateTime.MinValue;
            Execute(manager, delegate(TransactionManager mgr, DbCommand cmd)
            {
                cmd.CommandText = "select now();";
                dt = GenericDbHelper.ExecuteScalar<DateTime>(mgr, cmd, dt);
            });

            return dt;
        }

        public override void InitDatabase(TransactionManager manager)
        {
            //以下代码负责完成初始化数据库时所需要做的工作

            return;
        }
        public override void FixTreeTable(TransactionManager manager, string tbEntity, string tbTree, string colEntityId, string colEntityParentId, string colTreeEntityId, string colTreeEntityParentId)
        {
            Execute(manager, delegate(TransactionManager mgr, DbCommand cmd)
            {
                string sqlSelectChildId = String.Format("select {1} from {0} where {2}=?parent_id", tbEntity, colEntityId, colEntityParentId);
                string sqlSelectRootId = String.Format("select {1} from {0} where {2} is null or {2} not in (select {1} from {0})", tbEntity, colEntityId, colEntityParentId);
                string sqlInsertTreeInfo = String.Format(
                    "insert into {0} ({1}, {2}, level_no) values(?id, ?id, 0);" +
                    "insert into {0} ({1}, {2}, level_no) select ?id, {2}, level_no + 1 from {0} where {1}=?parent_id",
                    tbTree, colTreeEntityId, colTreeEntityParentId);

                cmd.CommandText = String.Format("delete from {0}", tbTree);
                cmd.ExecuteNonQuery();

                cmd.CommandText = sqlSelectRootId;
                List<int> rootIdList = new List<int>();
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        rootIdList.Add(reader.GetInt32(0));
                    }
                }

                cmd.CommandText = sqlInsertTreeInfo;
                cmd.Parameters.Add(MySqlClientUtils.CreateParameter("?id", MySqlDbType.Int32, null));
                cmd.Parameters.Add(MySqlClientUtils.CreateParameter("?parent_id", MySqlDbType.Int32, -1));
                foreach (int id in rootIdList)
                {
                    cmd.Parameters[0].Value = id;
                    cmd.ExecuteNonQuery();
                }

                foreach (int id in rootIdList)
                {
                    FixTreeTableSubNode(cmd, sqlSelectChildId, sqlInsertTreeInfo, id);
                }
            });
        }

        private void FixTreeTableSubNode(DbCommand cmd, string sqlSelectChildId, string sqlInsertTreeInfo, int parentId)
        {
            cmd.CommandText = sqlSelectChildId;
            cmd.Parameters[1].Value = parentId;
            List<int> idList = new List<int>();
            using (IDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    idList.Add(reader.GetInt32(0));
                }
            }

            cmd.CommandText = sqlInsertTreeInfo;
            foreach (int id in idList)
            {
                cmd.Parameters[0].Value = id;
                cmd.ExecuteNonQuery();
            }

            foreach (int id in idList)
            {
                FixTreeTableSubNode(cmd, sqlSelectChildId, sqlInsertTreeInfo, id);
            }
        }
    }
}
